﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Oracle.DataAccess.Client;
using Types.Enumerations.CCG;

namespace DataService.CrudClassGenerator
{
    /// <summary>
    /// Contains some utility methods
    /// </summary>
    public static class Util
    {
        #region [MERGE] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        /// <summary>
        /// Used to merge two command objects
        /// </summary>
        /// <param name="pToCmd">Left hand side DbCommand object</param>
        /// <param name="pFromCmd">Right hand side DbCommand object</param>
        public static void MergeCommands(this DbCommand pToCmd, DbCommand pFromCmd)
        {
            pToCmd.CommandText += string.Format("; {0}", pFromCmd.CommandText);
            pToCmd.MergeCommandPrms(pFromCmd);
        }

        /// <summary>
        /// Used to merge parameters of two command objects
        /// </summary>
        /// <param name="pToCmd">Left hand side DbCommand object</param>
        /// <param name="pFromCmd">Right hand side DbCommand object</param>
        public static void MergeCommandPrms(this DbCommand pToCmd, DbCommand pFromCmd)
        {
            DbParameter[] sqlPrmArr = new DbParameter[pFromCmd.Parameters.Count];
            pFromCmd.Parameters.CopyTo(sqlPrmArr, 0);
            pFromCmd.Parameters.Clear();

            pToCmd.Parameters.AddRange(sqlPrmArr);
        }

        /// <summary>
        /// Used to merge any two list object
        /// </summary>
        /// <typeparam name="T">Type of object</typeparam>
        /// <param name="pToCmd">Left hand side list object</param>
        /// <param name="pFromCmd">Right hand side list object</param>
        public static void MergeLists<T>(this List<T> pToCmd, List<T> pFromCmd)
        {
            pToCmd.AddRange(pFromCmd);
        }

        #endregion

        #region [CHANGE PARAMETER NAMES] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        /// <summary>
        /// Used to Change parameter names in command object
        /// </summary>
        /// <param name="pCmd">DbCommand object to be acted on</param>
        /// <param name="pIndex">Number to add parameter as suffix</param>
        /// <returns></returns>
        public static DbCommand ChangeParameterNames(this DbCommand pCmd, int pIndex)
        {
            return ChangeParameterNames(pCmd, pIndex.ToString());
        }

        /// <summary>
        /// Used to Change parameter names in command object
        /// </summary>
        /// <param name="pCmd">DbCommand object to be acted on</param>
        /// <param name="pSuffix">String to add parameter as suffix</param>
        /// <returns></returns>
        public static DbCommand ChangeParameterNames(this DbCommand pCmd, string pSuffix)
        {
            string cmdText = pCmd.CommandText;

            for (int i = 0; i < pCmd.Parameters.Count; i++)
            {
                cmdText = cmdText.ReplaceWholeWord(pCmd.Parameters[i].ParameterName, pCmd.Parameters[i].ParameterName + pSuffix);
                pCmd.Parameters[i].ParameterName = pCmd.Parameters[i].ParameterName + pSuffix;
            }
            pCmd.CommandText = cmdText;
            return pCmd;
        }

        /// <summary>
        /// Used to Change parameter names in command object list
        /// </summary>
        /// <param name="pCmd">DbCommand object list to be acted on</param>
        /// <param name="pIndex">Number to add parameter as suffix</param>
        /// <returns></returns>
        public static List<DbCommand> ChangeParameterNames(this List<DbCommand> pCmd, int pIndex)
        {
            return ChangeParameterNames(pCmd, pIndex.ToString());
        }

        /// <summary>
        /// Used to Change parameter names in command object list
        /// </summary>
        /// <param name="pCmd">DbCommand object list to be acted on</param>
        /// <param name="pSuffix">String to add parameter as suffix</param>
        /// <returns></returns>
        public static List<DbCommand> ChangeParameterNames(this List<DbCommand> pCmd, string pSuffix)
        {
            List<DbCommand> cmds = new List<DbCommand>();
            foreach (DbCommand cmd in pCmd)
            {
                cmds.Add(ChangeParameterNames(cmd, pSuffix));
            }
            return cmds;
        }

        /// <summary>
        /// Used to Change IdentityPrmName value in command object
        /// </summary>
        /// <param name="pCmd">DbCommand object to be acted on</param>
        /// <param name="pIdentityPrmName">IdentityPrmName value</param>
        /// <param name="pSuffix">String to add parameter as suffix</param>
        /// <returns></returns>
        public static DbCommand ChangeIdentityParameterName(this DbCommand pCmd, string pIdentityPrmName, string pSuffix)
        {
            pCmd.CommandText = pCmd.CommandText.ReplaceWholeWord(pIdentityPrmName, pIdentityPrmName + pSuffix);
            return pCmd;
        }

        #endregion

        #region [OTHERS] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        /// <summary>
        /// Used to add, necessary additional texts for transaction
        /// </summary>
        /// <param name="pCmd">DbCommand object to be acted on</param>
        public static void AddTransaction(this DbCommand pCmd)
        {
            string sql = string.Empty;

            if (DataServiceUtil.DatabaseType() == DatabaseTypes.MS_SQL)
            {
                sql = @"SET XACT_ABORT ON;
						BEGIN TRAN
									
							{0}
									
						COMMIT			
						";
            }
            else if (DataServiceUtil.DatabaseType() == DatabaseTypes.ORACLE)
            {
                sql = @"BEGIN
									
							{0}
									
							COMMIT;
							EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
						END;
						";
            }
            else
            {
                throw new ApplicationException(DataServiceUtil.UnknownDbType);
            }

            pCmd.CommandText = string.Format(sql, pCmd.CommandText);
        }

        /// <summary>
        /// Used to convert sql operator enums to related symbol
        /// </summary>
        /// <param name="pItemName">Operator item name</param>
        /// <returns></returns>
        public static string ToSqlOperator(this DbComparisonOperators pItemName)
        {
            switch (pItemName)
            {
                case DbComparisonOperators.Equal: return "=";
                case DbComparisonOperators.NotEqual: return "<>";
                case DbComparisonOperators.Greater: return ">";
                case DbComparisonOperators.GreaterOrEqual: return ">=";
                case DbComparisonOperators.Smaller: return "<";
                case DbComparisonOperators.SmallerOrEqual: return "<=";
                case DbComparisonOperators.Like: return "LIKE";
                case DbComparisonOperators.NotLike: return "NOT LIKE";
                case DbComparisonOperators.IsNull: return "IS NULL";
                case DbComparisonOperators.IsNotNull: return "IS NOT NULL";
                default: return string.Empty;
            }
        }

        /// <summary>
        /// This method is used to change comparison key as IN
        /// NOT: Details are written in method
        /// </summary>
        /// <param name="pCmd">"Insert, Update, Delete, Select" or "Save" DbCommand object to be acted on</param>
        /// <param name="pParameterName">The name of the parameter that comparison operator is replaced by IN</param>
        /// <param name="pValueList">Comparison data list. Like; "1,5,8"</param>
        /// <param name="pCrudType">Crud Type</param>
        public static void ConvertSqlComparisonOperatorToIN(this DbCommand pCmd, string pParameterName, string pValueList, CrudTypes pCrudType)
        {
            #region [DESCRIPTION]
            /*
			 * Note: 
			 * If parameter value is null or comparison operator is defined different then "=", error will occur.
			 * Forex: If there will be a search for "PersonID IN (1,5,8)", parameter shoul be defined as "prm.PersonID = 0"
			 * Default comparison operator is "Equal"
			 * 
			 * NOT: Operatörü IN ile değiştirilecek olan parametre, önce "=(Equal)"  operatörü ile tanımlanmalıdır. 
			 * Hiç tanımlanmamış, yani hiç değer ataması yapılmamaış bir parametre verildiğinde ya da 
			 * karşılaştırma operatörü "=" dışında bir operatör verildiğinde hata oluşur.
			 * Örneğin: "KisiKey IN (1,5,8)" şeklinde bir arama yapılacak ise; paramatre "prm.KisiKey = 0" olarak tanımlanmalı ve 
			 * bu parametrenin "ComparisonOperator" değeri değiştirilmemelidir. 
			 * Örneğin, "prm.ComparisonOperator.KisiKey = DbComparisonOperators.Greater" şeklinde bir tanımlama yapılmamalıdır.
			 * "ComparisonOperator" değeri verilmediğinde öntanımlı olan "Equal" kullanılır.
			*/
            #endregion

            string prm = pCrudType == CrudTypes.Update ? string.Format("@{0}W", pParameterName) : string.Format("@{0}", pParameterName);
            string oldPrm = string.Format("{0} = {1}", pParameterName, prm);
            string newPrm = string.Format("{0} IN ({1})", pParameterName, pValueList);
            pCmd.CommandText = pCmd.CommandText.ReplaceWholeWord(oldPrm, newPrm);
            pCmd.Parameters.RemoveAt(prm);
        }

        /// <summary>
        /// Used to add existence control to sql command
        /// </summary>
        /// <param name="pCmd">DbCommand object to be acted on</param>
        /// <param name="pCompOperator">Comparison operator ('=', '>', '>=' etc.).</param>
        /// <param name="pCompValue">Value to be compare with</param>
        /// <param name="pReturnMessage">Message to be returned after process</param>
        public static void UseToCompare(this DbCommand pCmd, DbComparisonOperators pCompOperator, string pCompValue, string pReturnMessage)
        {
            string sql = @" IF (({0}) {1} {2})
								BEGIN
									DECLARE @pReturnMsg VARCHAR(MAX) = 'CCGErr: {3} ';
									RAISERROR (@pReturnMsg ,17,1)
									RETURN
								END			
						  ";
            pCmd.CommandText = string.Format(sql, pCmd.CommandText, pCompOperator.ToSqlOperator(), pCompValue, pReturnMessage);
        }

        /// <summary>
        /// Used to check if command object includes Insert, Update or Delete query
        /// </summary>
        /// <param name="pCmd">DbCommand object to be compare</param>
        /// <returns></returns>
        public static bool HasIUDCommand(this DbCommand pCmd)
        {
            return HasIUDComand(pCmd.CommandText);
        }

        /// <summary>
        /// Used to check if command object includes Insert, Update or Delete query
        /// </summary>
        /// <param name="pInput">Sql CommandText to be compare</param>
        /// <returns></returns>
        public static bool HasIUDComand(this string pInput)
        {
            string input = pInput.ToUpper();
            if (input.IsContains("INSERT")) return true;
            if (input.IsContains("UPDATE")) return true;
            if (input.IsContains("DELETE")) return true;
            return false;
        }

        /// <summary>
        /// Used to check if table is ERROR_LOG or not
        /// </summary>
        /// <param name="pInput"></param>
        /// <returns></returns>
        public static bool IsTableExceptionLog(this string pInput)
        {
            return pInput.IsContains("FullExceptionLogTableName".ReadAppSetting<string>());
        }

        /// <summary>
        /// Used to get Identity Value which is created after Insert process.
        /// </summary>
        /// <param name="pCmd">DbCommand object to be acted on</param>
        /// <param name="pIdentityPrmName">Identity parameter name which defined in generated class</param>
        /// <param name="pPKColumnName">PrimaryKey column name (needs for Oracle)</param>
        /// <returns></returns>
        public static DbParameter GetIdentityValue(this DbCommand pCmd, string pIdentityPrmName, string pPKColumnName = "")
        {
            string identityPrmName = string.Format("{0}_NEWID", pIdentityPrmName);
            DbParameter newKey = null;

            if (DataServiceUtil.DatabaseType() == DatabaseTypes.MS_SQL)
            {
                if (pCmd.CommandText.IsContains(pIdentityPrmName))
                    pCmd.CommandText = string.Format("{0}; SET {1} = {2}", pCmd.CommandText, identityPrmName, pIdentityPrmName);
                else
                    pCmd.CommandText = string.Format("{0}; SET {1} = SCOPE_IDENTITY()", pCmd.CommandText, identityPrmName);

                newKey = ((SqlCommand)pCmd).Parameters.AddWithValue(identityPrmName, SqlDbType.Int);
                newKey.Direction = ParameterDirection.Output;
                return newKey;
            }
            else if (DataServiceUtil.DatabaseType() == DatabaseTypes.ORACLE)
            {
                string identityDeclare = string.Empty;
                string identitySet = string.Empty;

                if (pCmd.CommandText.IsContains(pIdentityPrmName))
                {
                    identitySet = string.Format("RETURNING {0} INTO {1} END;", pPKColumnName, pIdentityPrmName);
                }
                else
                {
                    identityDeclare = string.Format("DECLARE {0} NUMBER; BEGIN ", pIdentityPrmName);
                    identitySet = string.Format("RETURNING {0} INTO {1}; END;", pPKColumnName, pIdentityPrmName);
                }

                pCmd.CommandText = string.Join("", identityDeclare, pCmd.CommandText, identitySet);

                newKey = ((OracleCommand)pCmd).Parameters.Add(identityPrmName, OracleDbType.Int32, ParameterDirection.Output);
                return newKey;
            }
            else
            {
                throw new ApplicationException(DataServiceUtil.UnknownDbType);
            }
        }

        /// <summary>
        /// Used to delete sql transection commands from DbCommand object
        /// </summary>
        /// <param name="pCommandObject">DbCommand object to be acted on</param>
        public static void RemoveWriteLogTranSentences(this DbCommand pCommandObject)
        {
            pCommandObject.CommandText = RemoveWriteLogTranSentences(pCommandObject.CommandText);
        }

        /// <summary>
        /// Used to delete sql transection commands from DbCommand object
        /// </summary>
        /// <param name="pCommendText">DbCommand.CommandText value</param>
        /// <returns></returns>
        public static string RemoveWriteLogTranSentences(this string pCommendText)
        {
            pCommendText = pCommendText.ReplaceWholeWord("BEGIN TRAN", string.Empty)
                .ReplaceWholeWord("COMMIT", string.Empty)
                .ReplaceWholeWord("IF @@ERROR<>0  BEGIN ROLLBACK RETURN END", string.Empty);
            return pCommendText;
        }

        /// <summary>
        /// Used to remove "space" and other special characters from CommandText. 
        /// This method especially is used for Oracle, but also it can be used for MSSQL.
        /// </summary>
        /// <param name="pCommandObject">DbCommand object to be acted on</param>
        public static void PrepareCommandText(this DbCommand pCommandObject)
        {
            pCommandObject.CommandText = PrepareCommandText(pCommandObject.CommandText);
        }

        /// <summary>
        /// Used to remove "space" and other special characters from CommandText. 
        /// This method especially is used for Oracle, but also it can be used for MSSQL.
        /// </summary>
        /// <param name="pCommandText">DbCommand.CommandText value</param>
        /// <returns></returns>
        public static string PrepareCommandText(this string pCommandText)
        {
            pCommandText = pCommandText.Trim();                     //Remove start and end SPACEs
            pCommandText = pCommandText.Replace("\t", " ");         //Remove TABs
            pCommandText = pCommandText.Replace("\r\n", " ");       //Remove CARRIAGE RETURNs and LINE FEEDs
            do
            {
                pCommandText = pCommandText.Replace("  ", " ");     //Remove double SPACEs
            } while (pCommandText.IndexOf("  ") > -1);
            do
            {
                pCommandText = pCommandText.Replace(" ;", ";");     //Remove SPACEs before SEMICOLONs
            } while (pCommandText.IndexOf(" ;") > -1);
            do
            {
                pCommandText = pCommandText.Replace(";;", ";");     //Remove double SEMICOLONs
            } while (pCommandText.IndexOf(";;") > -1);

            return pCommandText;
        }

        /// <summary>
        /// Used to add, necessary additional texts to be able to paging
        /// </summary>
        /// <param name="pCmd">DbCommand object to be acted on</param>
        /// <param name="pSortingColumn">Column name for sorting</param>
        /// <param name="pDesc">Sorting type (true for "DESC" and false for "ASC")</param>
        /// <param name="pPageSize">Number of rows for each page</param>
        /// <param name="pPageIndex">Page number</param>
        public static void AddPaging(this DbCommand pCmd, string pSortingColumn, bool pDesc, int pPageSize, int pPageIndex)
        {
            string s = string.Format("ROW_NUMBER() OVER(ORDER BY {0} {1}) AS RwID, MT.*", pSortingColumn, pDesc == true ? "DESC" : "ASC");
            pCmd.CommandText = pCmd.CommandText.Replace("MT.*", s);

            string sql = @"
				WITH CCGPagingCTE AS (
	
					{0}
				)
				SELECT *
				FROM CCGPagingCTE
				WHERE RwID BETWEEN @pPageIndex * @pPageSize and @pPageIndex * @pPageSize + @pPageSize
				";

            if (DataServiceUtil.DatabaseType() == DatabaseTypes.ORACLE) sql = sql.Replace("@", ":");

            pCmd.CommandText = string.Format(sql, pCmd.CommandText);

            if (DataServiceUtil.DatabaseType() == DatabaseTypes.MS_SQL)
            {
                ((SqlCommand)pCmd).Parameters.AddWithValue("@pPageIndex", pPageIndex);
                ((SqlCommand)pCmd).Parameters.AddWithValue("@pPageSize", pPageSize);
            }
            else if (DataServiceUtil.DatabaseType() == DatabaseTypes.ORACLE)
            {
                ((OracleCommand)pCmd).Parameters.Add("@pPageIndex", pPageIndex);
                ((OracleCommand)pCmd).Parameters.Add("@pPageSize", pPageSize);
            }
            else
            {
                throw new ApplicationException(DataServiceUtil.UnknownDbType);
            }
        }

        /// <summary>
        /// Used to convert SqlCommand list to DbCommand list
        /// </summary>
        /// <param name="pCommandList">DbCommand list to be acted on</param>
        /// <returns></returns>
        public static List<DbCommand> ToDbCommandList(this List<SqlCommand> pCommandList)
        {
            return pCommandList.ConvertAll(t => { return (DbCommand)t; });
        }

        /// <summary>
        /// Used to convert OracleCommand list to DbCommand list
        /// </summary>
        /// <param name="pCommandList">DbCommand list to be acted on</param>
        /// <returns></returns>
        public static List<DbCommand> ToDbCommandList(this List<OracleCommand> pCommandList)
        {
            return pCommandList.ConvertAll(t => { return (DbCommand)t; });
        }

        /// <summary>
        /// Used to bind an Identity parameter to required column
        /// </summary>
        /// <param name="pCmd">Command object to be acted on</param>
        /// <param name="pColumnName">Required column name to bind identity parameter</param>
        /// <param name="pIdentityPrmName">IdentityPrmName value of table class which produces the identity value</param>
        public static void BindAnIdentityParameter(this DbCommand pCmd, string pColumnName, string pIdentityPrmName)
        {
            string clmnName = string.Format("@{0}", pColumnName);
            if (pCmd.CommandText.Contains("UPDATE")) clmnName = string.Format("@{0}S", pColumnName);

            pCmd.CommandText = pCmd.CommandText.ReplaceWholeWord(clmnName, pIdentityPrmName);
        }


        #endregion
    }
}
